#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys
import json
import db_conf
import cgi
import os
con = None

try:
    config = db_conf.getConfig('metabvis.conf')
    con = psycopg2.connect(host=db_conf.getDBHost(config), database=db_conf.getDBName(config), port=db_conf.getDBPort(config), user=db_conf.getUser(config), password=db_conf.getPassword(config)) 
    qform = cgi.FieldStorage()
    eid = qform.getvalue('experiment_id')
    cur = con.cursor()
    #e.experiment_id, p.full_name, e.date,
    select_experiments = """SELECT e.title, o.organism_text, st.bio_sample_type_text, 
count(csg.chromatogram_id) as files, e.date, p.full_name, e.experiment_id 
FROM experiment e LEFT JOIN people p ON e.experimenter = p.user_id
LEFT JOIN chromatogram_sample_group csg ON e.experiment_id =
csg.experiment_id
LEFT JOIN sample_group sg ON sg.sample_group_id = csg.sample_group_id
LEFT JOIN strain s ON  sg.strain_id = s.strain_id
LEFT JOIN organism o ON s.organism_id = o.organism_id
LEFT JOIN bio_sample_type st ON sg.bio_sample_type_id =
st.bio_sample_type_id where e.archived != 1 
GROUP BY e.experiment_id, p.full_name, e.date, e.title,
st.bio_sample_type_text, o.organism_text
ORDER BY e.experiment_id"""

    """
    if (eid != None):
        select_experiments = "select sample_group_id, name from sample_group where experiment_id = '" + eid + "' or is_global = 't'"
    cur.execute(select_experiments)          
    rows = cur.fetchall()
    print "Content-type: text/html;charset=utf-8\r\n"
    j = []
    pa = {}
    pa['aaData'] = []
    if (eid == None):
        for row in rows:
	    pi = []
	    for r in range(len(row)):
	        if (r == 0):
                    pi.append('<img src="images/in_icon.png">')# "<a href=javascript:updateExperiment('" + str(row[r]) + "')>edit</a>")
                    pi.append(row[r])
                elif (r == 4):
                    pi.append(str(row[r]).split(" ")[0])
                else:
                    pi.append(row[r])
            pa['aaData'].append(pi)
    else:
	for row in rows:
            pi = []
            for r in range(len(row)):
                pi.append(row[r])
            #pi.append(None)
            pa['aaData'].append(pi)
    """
    cur.execute('SELECT project_id, project_name from project order by project_name asc')
    rows = cur.fetchall()
    print "Content-type: text/html;charset=utf-8\r\n"
    j = []
    pa = {}
    pa['projects'] = []
    for row in rows:
        pi = []
        for r in range(len(row)):
                pi.append(row[r])
        pa['projects'].append(pi)
    cur.execute("select organism_id, organism_text from organism")
    rows = cur.fetchall()
    pc = {}
    for row in rows:
        pc[row[0]] = row[1]
    cur.execute("select bio_sample_type_id, bio_sample_type_text from bio_sample_type")
    rows = cur.fetchall()
    pd = {}
    for row in rows:
        pd[row[0]] = row[1]
    pa['biosampleData'] = pd
    username = None
    user = {}
    try:
        username = os.environ['REMOTE_USER']
	cur.execute("select user_name, full_name, sample_name_abbreviation, user_id from people where user_name = '" + username + "';")
	row = cur.fetchone()
	user["user_name"]=row[0]
	user["full_name"]=row[1]
	user["abbrev"]=row[2]
	user["id"]=row[3]
    except KeyError:
        username = ""
    pa['user'] = user
    print json.dumps(pa)
except psycopg2.DatabaseError, e:
    print 'Error %s' % e    
    sys.exit(1)    
finally:
    
    if con:
        con.close()
